using Devart.Data.SqlServer;
using Devart.Data.SqlServer.SqlTypes;
using System.Data;

namespace SqlVectorConsoleApp
{
        internal class Program
        {
                static void Main(string[] args)
                {
                        string connString = @"Data Source=localhost;Integrated Security=False;User ID=sa;TrustServerCertificate=True;Database=dbname";

                        CreateTableAndProcedure(connString);
                        InsertData(connString);
                        ReadVectorData(connString);
                        ReadVectorParam(connString);
                }

                private static void CreateTableAndProcedure(string connectionString)
                {
                        using (SqlConnection connection = new SqlConnection(connectionString))
                        {
                                connection.Open();
                                using (SqlCommand command = connection.CreateCommand())
                                {
                                        string tableCreate = "CREATE TABLE [VectorTable] (Id INT IDENTITY(1,1) PRIMARY KEY, Data vector(5) NULL);";
                                        string storedProcCreate = $@"CREATE PROCEDURE GetVectorProc
                                                @data vector(5),
                                                @outputData vector(5) OUTPUT
                                                AS
                                                BEGIN
                                                SET NOCOUNT ON;
                                                INSERT INTO [VectorTable] (Data) VALUES(@Data);
                                                SELECT TOP (1) @outputData = Data FROM [VectorTable] ORDER BY Id DESC;
                                                END;";

                                        command.CommandText = "DROP TABLE IF EXISTS [VectorTable]";
                                        command.ExecuteNonQuery();
                                        command.CommandText = tableCreate;
                                        command.ExecuteNonQuery();

                                        command.CommandText = "DROP PROCEDURE IF EXISTS GetVectorProc";
                                        command.ExecuteNonQuery();
                                        command.CommandText = storedProcCreate;
                                        command.ExecuteNonQuery();
                                }
                        }
                }

                private static void InsertData(string connectionString)
                {
                        using (SqlConnection connection = new SqlConnection(connectionString))
                        {
                                connection.Open();
                                using (SqlCommand command = new SqlCommand("INSERT INTO VectorTable (Data) VALUES (@data)", connection))
                                {
                                        // Insert Vector value
                                        var vectorParam = new SqlParameter("@data", SqlType.Vector);
                                        float[] values = new float[] { 1.1f, 2.22f, 3.333f, 4.0f, 5.5f };
                                        vectorParam.SqlValue = new SqlVector<float>(values);
                                        command.Parameters.Add(vectorParam);
                                        command.ExecuteNonQuery();

                                        command.Parameters.Clear();
                                        command.Parameters.AddWithValue("@data", new SqlVector<float>(new float[] { 11.11f, 22.22f, 33.33f, 44.44f, 55.55f }));
                                        command.ExecuteNonQuery();

                                        // Insert null value
                                        command.Parameters.Clear();
                                        vectorParam.Value = SqlVector<float>.CreateNull(5);
                                        command.Parameters.Add(vectorParam);
                                        command.ExecuteNonQuery();
                                }
                        }
                }

                private static void ReadVectorData(string connectionString)
                {
                        using (SqlConnection connection = new SqlConnection(connectionString))
                        {
                                connection.Open();
                                using (SqlCommand command = new SqlCommand("SELECT Data FROM VectorTable", connection))
                                {
                                        using (SqlDataReader reader = command.ExecuteReader())
                                        {
                                                while (reader.Read())
                                                {
                                                        if (!reader.IsDBNull(0))
                                                        {
                                                                // Read Vector with GetSqlJson
                                                                SqlVector<float> sqlVector = reader.GetSqlVector<float>(0);
                                                                Console.WriteLine($"Vector data by GetSqlVector: {sqlVector}");

                                                                // Read Vector with GetString
                                                                string vectorString = reader.GetString(0);
                                                                Console.WriteLine($"Vector data by GetString: {vectorString}");
                                                        }
                                                        else
                                                                Console.WriteLine("Vector data: NULL");
                                                }
                                        }
                                }
                        }
                }

                private static void ReadVectorParam(string connectionString)
                {
                        using (SqlConnection connection = new SqlConnection(connectionString))
                        {
                                connection.Open();
                                using (SqlCommand command = new SqlCommand("GetVectorProc", connection))
                                {
                                        command.CommandType = CommandType.StoredProcedure;

                                        var inputData = new SqlVector<float>(new float[] { 10f, 20f, 30f, 40f, 50f });
                                        command.Parameters.Add(new SqlParameter("@data", SqlType.Vector) { Value = inputData });

                                        SqlParameter vectorParam = new SqlParameter("@outputData", SqlType.Vector) { Direction = ParameterDirection.Output };
                                        vectorParam.Value = SqlVector<float>.CreateNull(5);
                                        command.Parameters.Add(vectorParam);

                                        command.ExecuteNonQuery();
                                        var vectorValue = (SqlVector<float>)vectorParam.Value;
                                        if (vectorValue.IsNull)
                                                Console.WriteLine("Vector data: NULL");
                                        else
                                                Console.WriteLine($"Vector data from StoredProcedure: [{string.Join(", ", vectorValue.Memory.ToArray())}]");
                                }
                        }
                }
        }
}